package com.scau.wrongtitle.repository;

import com.scau.wrongtitle.domin.UserInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface UserInfoRepository extends JpaRepository<UserInfo, Long> {

    @Query(value = "select count(*) from (select * from t_wrong_title as t_w where t_w.question_grade = :grade) as T," +
            "(select * from t_title_record as t_r where t_r.user_id = :id) as S " +
            "where T.question_id = S.question_id ;", nativeQuery = true)
    int getUserTitleSum(@Param("id") long id, @Param("grade") String grade);

    @Query(value = "select count(*) from (select * from t_wrong_title as t_w where t_w.question_grade = :grade and t_w.question_subject= :subject) as T," +
            "(select * from t_title_record as t_r where t_r.user_id = :id) as S " +
            "where T.question_id = S.question_id ;", nativeQuery = true)
    int getUserTitleSum(@Param("id") long id, @Param("grade") String grade, @Param("subject")String subject);

    @Query(value = "select question_grade from t_wrong_title as T, " +
            "(select * from t_title_record as t_r where t_r.user_id = :id) as S  " +
            "where T.question_id = S.question_id group by T.question_grade;", nativeQuery = true)
    List<String> getUserGrades(@Param("id") long id);

    UserInfo findById(long id);
}
